﻿using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ToolUtil
{
    /// <summary>
    /// excel转table
    /// </summary>
    public class OpenXMLHelper
    {
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="filepath"></param>
        /// <param name="dts"></param>
        /// <returns></returns>
        public static string ExportToExcel(string filepath, DataTable dt)
        {
            //var result = new ResultValue();
            string flag = string.Empty;
            SpreadsheetDocument spreadsheetDocument = null;
            var graphics = Graphics.FromHwnd(IntPtr.Zero);
            try
            {
                var generatedSpreadsheetDocument = new GeneratedSpreadsheetDocument();
                var exist = File.Exists(filepath);
                if (exist)
                    spreadsheetDocument = SpreadsheetDocument.Open(filepath, true);
                else
                    spreadsheetDocument = generatedSpreadsheetDocument.CreatePackage(filepath);
                var workbookPart = spreadsheetDocument.WorkbookPart ?? spreadsheetDocument.AddWorkbookPart();
                if (workbookPart.Workbook == null)
                    workbookPart.Workbook = new Workbook();
                var wordbook = workbookPart.Workbook;
                var sheets = wordbook.GetFirstChild<Sheets>();
                if (sheets == null)
                {
                    sheets = new Sheets();
                    wordbook.Append(sheets);
                }
                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                var worksheet = worksheetPart.Worksheet = new Worksheet();

                var sheetId = sheets.Elements<Sheet>().Count() == 0 ? 1 : sheets.Elements<Sheet>().Max(q => q.SheetId.Value) + 1;
                var sheetName = dt.TableName;

                var i = 1;
                while (sheets.Elements<Sheet>().Any(q => q.Name == sheetName))
                {
                    sheetName = $"{dt.TableName}({i++})";
                }

                var sheet = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = sheetName };
                sheets.Append(sheet);

                var sheetData = new SheetData();
                worksheet.Append(sheetData);

                var cellColumns = new Columns();
                worksheet.Append(cellColumns);

                var sharedStringTablePart = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault() ?? workbookPart.AddNewPart<SharedStringTablePart>();

                var columns = dt.Columns;

                var head_row = new Row();

                var j = 1U;
                var font = new System.Drawing.Font("宋体", 12);

                foreach (DataColumn col in columns)
                {
                    var text = col.ColumnName;
                    var index = InsertSharedStringItem(text, sharedStringTablePart);
                    var cell = new Cell { CellValue = new CellValue(index.ToString()), DataType = new EnumValue<CellValues>(CellValues.SharedString), StyleIndex = 1U };
                    head_row.Append(cell);

                    cellColumns.Append(new Column { Min = j, Max = j, Width = graphics.MeasureString(text, font).Width / 7, BestFit = true, CustomWidth = true });
                    j++;
                }

                sheetData.Append(head_row);

                font = new System.Drawing.Font("宋体", 11);

                foreach (DataRow dr in dt.Rows)
                {
                    var row = new Row();

                    i = 0;
                    foreach (DataColumn col in columns)
                    {
                        var dc = dr[col.ColumnName];
                        var text = dc?.ToString() ?? String.Empty;
                        var index = InsertSharedStringItem(text, sharedStringTablePart);
                        var cell = new Cell { CellValue = new CellValue(index.ToString()), DataType = new EnumValue<CellValues>(CellValues.SharedString), StyleIndex = 0U };
                        row.Append(cell);

                        var w = text.Split(Environment.NewLine.ToCharArray()).Max(q => graphics.MeasureString(q, font).Width / 7);
                        var column = cellColumns.Elements<Column>().ElementAt(i++);

                        if (column.Width.Value < w)
                            column.Width = w;
                    }

                    sheetData.Append(row);
                }

                spreadsheetDocument.Close();
                flag = "导出成功，保存路径" + filepath;
            }
            catch (Exception ex)
            {
                flag = "导出Excel失败，错误原因：" + ex.GetBaseException().Message;
            }
            finally
            {
                graphics.Dispose();
                spreadsheetDocument?.Dispose();
            }

            return flag;
        }
        /// <summary>
        /// 将dgv列表数据转换为datatable数据
        /// </summary>
        /// <param name="dgv">当前dgv列表对象</param>
        /// <returns>datatable对象</returns>
        public static System.Data.DataTable GetDgvToTable(DataGridView dgv)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            // 列强制转换
            for (int count = 0; count < dgv.Columns.Count; count++)
            {
                System.Data.DataColumn dc = new System.Data.DataColumn(dgv.Columns[count].HeaderText.ToString());
                dt.Columns.Add(dc);
            }
            // 循环行
            for (int count = 0; count < dgv.Rows.Count; count++)
            {
                DataRow dr = dt.NewRow();
                for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
                {
                    string nr = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
                    if (nr.Contains('\x001e'.ToString()))
                    {
                        nr = nr.Replace('\x001e'.ToString(), "@");
                        nr = nr.Replace('\x001f'.ToString(), "|");
                        nr = nr.Replace('\x001d'.ToString(), "");
                    }
                    dr[countsub] = nr;
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="fileName">保存文件路径</param>
        /// <param name="exportTable">数据源</param>
        public static void ExportExcelFile(string fileName, DataTable exportTable)
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                FrmProcess process2 = new FrmProcess();
                process2.progressBar1.Maximum = exportTable.Rows.Count;
                FrmProcess process = process2;
                process.Show();
                WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);

                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = exportTable.TableName };

                sheets.Append(sheet);
                Row headerRow = new Row();

                List<String> columns = new List<string>();
                foreach (System.Data.DataColumn column in exportTable.Columns)
                {
                    columns.Add(column.ColumnName);
                    Cell cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }

                sheetData.AppendChild(headerRow);
                int j = 0;
                foreach (DataRow dsrow in exportTable.Rows)
                {
                    j++;
                    System.Windows.Forms.Application.DoEvents();
                    process.progressBar1.Value = j;
                    process.label1.Text = j + "/" + exportTable.Rows.Count;
                    Row newRow = new Row();
                    foreach (String col in columns)
                    {
                        Cell cell = new Cell();
                        cell.DataType = CellValues.String;
                        cell.CellValue = new CellValue(dsrow[col].ToString());
                        newRow.AppendChild(cell);
                    }
                    sheetData.AppendChild(newRow);
                }
                process.Close();
                workbookPart.Workbook.Save();
            }
        }

        // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
        // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (shareStringPart.SharedStringTable == null)
                shareStringPart.SharedStringTable = new SharedStringTable();

            var i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (var item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                    return i;

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));

            return i;
        }
        /// <summary>
        /// excel to table
        /// </summary>
        /// <param name="pathFilename">excel文件路径</param>
        /// <param name="worksheetName">工作表名</param>
        /// <returns></returns>
        public static DataTable ExcelWorksheetToDataTable(string pathFilename, string worksheetName)
        {
            DataTable dt = new DataTable(worksheetName);
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathFilename, false))
            {
                // 打开工作表
                Sheet theSheet = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName).FirstOrDefault();
                if (theSheet == null)
                    throw new Exception("找不到工作表:" + worksheetName);

                WorksheetPart wsPart = (WorksheetPart)(document.WorkbookPart.GetPartById(theSheet.Id));
                Worksheet workSheet = wsPart.Worksheet;

                string dimensions = workSheet.SheetDimension.Reference.InnerText;
                //  获取此工作表的维度，例如“B2:F4”
                int numOfColumns = 0;
                int numOfRows = 0;
                CalculateDataTableSize(dimensions, ref numOfColumns, ref numOfRows);

                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();

                string[,] cellValues = new string[numOfColumns, numOfRows];
                int colInx = 0;
                int rowInx = 0;
                string value = "";
                SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;

                //  遍历每一行OpenXML数据，并将每个单元格的值存储在[，]字符串数组中的适当插槽中。
                foreach (Row row in rows)
                {
                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        //假设每行的每一列都有一个XML元素
                        Cell cell = row.Descendants<Cell>().ElementAt(i);
                        if (cell.CellValue == null || cell.CellReference == null)
                            continue; //  eg when an Excel cell contains a blank string

                        // 将此Excel单元格的CellAddress转换为数组中基于0的偏移量（例如“G13”->[6，12]）
                        colInx = GetColumnIndexByName(cell.CellReference);             // 例如“C”->2（基于0）
                        rowInx = GetRowIndexFromCellAddress(cell.CellReference) - 1;     //需要以0为基础

                        //  获取此单元格中的值
                        value = cell.CellValue.InnerXml;

                        if (cell.DataType != null && (cell.DataType.Value == CellValues.SharedString || cell.DataType.Value == CellValues.String || cell.DataType.Value == CellValues.Number))
                        {
                            value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
                        }
                        bool a = Regex.IsMatch(value, @"^[+-]?\d*[.]?\d*$");
                        if (a && value.Contains("."))
                        {
                            try
                            {
                                double b = double.Parse(value);
                                //b =Math.Round(b, 2);
                                //value = b.ToString("f2");
                                value = b.ToString("#0.00");
                                value = Regex.Replace(value, @"[^\d.\d]", "");
                            }
                            catch { }
                        }
                        cellValues[colInx, rowInx] = value;
                    }
                }

                //  将字符串数组复制到数据表中
                //  目前没有尝试确定哪些列应该是数字，而不是字符串
                for (int col = 0; col < numOfColumns; col++)
                    dt.Columns.Add(cellValues[col, 0]);

                for (int row = 1; row < numOfRows; row++)
                {
                    DataRow dataRow = dt.NewRow();
                    for (int col = 0; col < numOfColumns; col++)
                    {
                        dataRow.SetField(col, cellValues[col, row]);
                    }
                    dt.Rows.Add(dataRow);
                }

                return dt;
            }
        }

        private static void CalculateDataTableSize(string dimensions, ref int numOfColumns, ref int numOfRows)
        {
            //此工作表包含多少列和行数据？
            //我们将从Excel文件中读取维度字符串，并根据该字符串计算大小。
            //例如“B1:F4”->我们需要6列4行。
            //（我们故意忽略左上角的单元格地址，只使用右下角的单元格地址。）
            try
            {
                string[] parts = dimensions.Split(':');     // eg"B1:F4"
                if (parts.Length != 2)
                    throw new Exception("Couldn't find exactly *two* CellAddresses in the dimension");

                numOfColumns = 1 + GetColumnIndexByName(parts[1]);
                //A=1，B=2，C=3（基于1的值），因此F4将返回6列
                numOfRows = GetRowIndexFromCellAddress(parts[1]);
            }
            catch
            {
                throw new Exception("Could not calculate maximum DataTable size from the worksheet dimension:" + dimensions);
            }
        }

        public static int GetRowIndexFromCellAddress(string cellAddress)
        {
            // 将Excel CellReference列转换为基于1的行索引
            //例如“D42”->42
            //“F123”->123
            string rowNumber = System.Text.RegularExpressions.Regex.Replace(cellAddress, "[^0-9 _]", "");
            return int.Parse(rowNumber);
        }

        public static int GetColumnIndexByName(string cellAddress)
        {
            //将Excel CellReference列转换为基于0的列索引
            //例如“D42”->3
            //“F123”->5
            var columnName = System.Text.RegularExpressions.Regex.Replace(cellAddress, "[^A-Z_]", "");
            int number = 0, pow = 1;
            for (int i = columnName.Length - 1; i >= 0; i--)
            {
                number += (columnName[i] - 'A' + 1) * pow;
                pow *= 26;
            }
            return number - 1;
        }
    }
}
